package com.niit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.niit.pojo.Product;
import com.niit.utils.JDBCUtils;

public class ProductDao {
	
	public List<Product> listProduct(String productName,Double price,String productType,Integer page,Integer limit) throws SQLException{
//		获得数据库链接
		Connection connection = JDBCUtils.getConnection();
//		where 1=1可以让拼接多个条件时不管用户是否传入参数，SQL语句都可以正常执行
		StringBuilder sql = new StringBuilder("SELECT * from product where 1=1");
//		多条件的筛选，先判断是否为空再拼接到SQL语句
		if( productName != null && "".equals(productName)) {
			sql.append("and product like '%");
			sql.append(productName);
			sql.append("%'");
		}
		if( price != null ) {
			sql.append("and price ="+price);
		}
		if( productType != null && "".equals(productType)) {
			sql.append("and product_type = "+productType);
		}
//		分页
		if(page != null && limit != null) {
			sql.append(" limit "+(page * limit -1)+"," +limit);
		}
		PreparedStatement ps = connection.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
//		将结果集封装到集合
		List<Product> list = new ArrayList<>();
		while (rs.next()) {
			Product product = new Product(rs.getInt("id"),rs.getString("product_name")
					,rs.getDouble("price"),rs.getString("product_type"),rs.getInt("repertory"),rs.getDate("create_time"));
			list.add(product);
			
		}
		return list;
		
	}

}
